package csu.web.mypetstore.persistence.impl;

import csu.web.mypetstore.domain.Account;
import csu.web.mypetstore.persistence.AccountDao;
import csu.web.mypetstore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class AccountDaoImpl implements AccountDao {

    private final String FIND_USER_BY_USERNAME = "SELECT * FROM account WHERE userid=?";
    public boolean findUserByUsername(String username){
        Boolean accountResult = false;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_USER_BY_USERNAME);
            preparedStatement.setString(1, username);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                accountResult = true;
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return accountResult;
    }

    private static final String GET_ACCOUNT_BY_USERNAME_AND_PASSWORD = "SELECT " +
            "SIGNON.USERNAME," +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME " +
            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
            "WHERE ACCOUNT.USERID = ? AND SIGNON.PASSWORD = ? " +
            "AND SIGNON.USERNAME = ACCOUNT.USERID " +
            "AND PROFILE.USERID = ACCOUNT.USERID " +
            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";

    private static final String GET_ACCOUNT_BY_Phone = "SELECT " +
            "SIGNON.USERNAME," +
            "ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS," +
            "ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2," +
            "ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE," +
            "PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId," +
            "PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption," +
            "BANNERDATA.BANNERNAME " +
            "FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA " +
            "WHERE ACCOUNT.Phone = ? " +
            "AND SIGNON.USERNAME = ACCOUNT.USERID " +
            "AND PROFILE.USERID = ACCOUNT.USERID " +
            "AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY"
            ;

    private static final String INSTERACCOUNT_TO_ACCOUNT = "INSERT INTO " +
            "account (userid,email,firstname,lastname,status,addr1,addr2,city,state,zip,country,phone)" +
            "VALUES (?,?,?,?,?,?,?,?,?,?,?,?)";

    private static final String INSTERACCOUNT_TO_SIGNON = "INSERT INTO " +
            "signon (username,password)" +
            "VALUES (?,?)";
    private static final String INSTERACCOUNT_TO_PROFILE = "INSERT INTO " +
            "profile (userid,langpref,favcategory,mylistopt,banneropt)" +
            "VALUES (?,?,?,?,?)";
    private static final String CHANGEACCOUNT_TO_ACCOUNT = "UPDATE ACCOUNT SET " +
            "email=?,firstname=?,lastname=?,status=?,addr1=?,addr2=?,city=?,state=?,zip=?,country=?,phone=? " +
            "WHERE userid=?";

    private static final String CHANGEACCOUNT_TO_SIGNON = "UPDATE SIGNON SET " +
            "password=? " +
            "WHERE username=?";

    private static final String CHANGEACCOUNT_TO_PROFILE = "UPDATE PROFILE SET " +
            "langpref=?,favcategory=? " +
            "WHERE userid=?";


    public String Phone(String number){
        String msg="";
        if(number == null || number.equals("")){
            msg = "Phone is empty!";
        }
        if(number.matches("\\d{8,11}")){
            msg = "Phone is illeague!";
        }
        return msg;
    }
    @Override
    public void changeAccount_3(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement insertStatement = connection.prepareStatement(CHANGEACCOUNT_TO_PROFILE);
            insertStatement.setString(1,account.getLanguagePreference());
            insertStatement.setString(2,account.getFavouriteCategoryId());
            insertStatement.setString(3,account.getUsername());
            insertStatement.executeUpdate();
            insertStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void changeAccount_2(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement insertStatement = connection.prepareStatement(CHANGEACCOUNT_TO_SIGNON);
            insertStatement.setString(1,account.getPassword());
            insertStatement.setString(2,account.getUsername());
            insertStatement.executeUpdate();
            insertStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void changeAccount(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement insertStatement = connection.prepareStatement(CHANGEACCOUNT_TO_ACCOUNT);
            insertStatement.setString(1, account.getEmail());
            insertStatement.setString(2, account.getFirstName());
            insertStatement.setString(3, account.getLastName());
            insertStatement.setString(4, account.getStatus());
            insertStatement.setString(5, account.getAddress1());
            insertStatement.setString(6, account.getAddress2());
            insertStatement.setString(7, account.getCity());
            insertStatement.setString(8, account.getState());
            insertStatement.setString(9, account.getZip());
            insertStatement.setString(10, account.getCountry());
            insertStatement.setString(11, account.getPhone());
            insertStatement.setString(12, account.getUsername());
            insertStatement.executeUpdate();
            System.out.println("account!");
            insertStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void insertAccount(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement insertStatement = connection.prepareStatement(INSTERACCOUNT_TO_SIGNON);
            insertStatement.setString(1, account.getUsername());
            insertStatement.setString(2, account.getPassword());
            insertStatement.executeUpdate();
            System.out.println("signon!");
            insertStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void insertAccount_2(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement insertStatement2 = connection.prepareStatement(INSTERACCOUNT_TO_ACCOUNT);
            insertStatement2.setString(1, account.getUsername());
            insertStatement2.setString(2, account.getEmail());
            insertStatement2.setString(3, account.getFirstName());
            insertStatement2.setString(4, account.getLastName());
            insertStatement2.setString(5, account.getStatus());
            insertStatement2.setString(6, account.getAddress1());
            insertStatement2.setString(7, account.getAddress2());
            insertStatement2.setString(8, account.getCity());
            insertStatement2.setString(9, account.getState());
            insertStatement2.setString(10, account.getZip());
            insertStatement2.setString(11, account.getCountry());
            insertStatement2.setString(12, account.getPhone());
            insertStatement2.executeUpdate();

            insertStatement2.close();
            connection.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    public void insertAccount_3(Account account){
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement insertStatement = connection.prepareStatement(INSTERACCOUNT_TO_PROFILE);
            insertStatement.setString(1, account.getUsername());
            insertStatement.setString(2, account.getLanguagePreference());
            insertStatement.setString(3, account.getFavouriteCategoryId());
            insertStatement.setInt(4,1);
            insertStatement.setInt(5,1);
            insertStatement.executeUpdate();

            insertStatement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Account getAccountByUsername(String username) {
        return null;
    }

    @Override
    public Account getAccountByUsernameAndPassword(Account account) {
        Account accountResult = null;
        try {
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_USERNAME_AND_PASSWORD);
            preparedStatement.setString(1, account.getUsername());
            preparedStatement.setString(2, account.getPassword());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                accountResult = this.resultSetToAccount(resultSet);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }catch (Exception e){
            e.printStackTrace();
        }
        return accountResult;
    }

    @Override
    public Account getAccountByPhone(Account account) {
        Account accountResult = null;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_ACCOUNT_BY_Phone);
            preparedStatement.setString(1,account.getPhone());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                accountResult = this.resultSetToAccount(resultSet);
            }
            resultSet.close();
            preparedStatement.close();
            connection.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return accountResult;
    }

    private Account resultSetToAccount(ResultSet resultSet) throws Exception{
        Account account = new Account();
        account.setUsername(resultSet.getString("username"));
//        account.setPassword(resultSet.getString("password"));
        account.setEmail(resultSet.getString("email"));
        account.setFirstName(resultSet.getString("firstName"));
        account.setLastName(resultSet.getString("lastName"));
        account.setStatus(resultSet.getString("status"));
        account.setAddress1(resultSet.getString("address1"));
        account.setAddress2(resultSet.getString("address2"));
        account.setCity(resultSet.getString("city"));
        account.setState(resultSet.getString("state"));
        account.setZip(resultSet.getString("zip"));
        account.setCountry(resultSet.getString("country"));
        account.setPhone(resultSet.getString("phone"));
        account.setFavouriteCategoryId(resultSet.getString("favouriteCategoryId"));
        account.setLanguagePreference(resultSet.getString("languagePreference"));
        account.setListOption(resultSet.getInt("listOption") == 1);
        account.setBannerOption(resultSet.getInt("bannerOption") == 1);
        account.setBannerName(resultSet.getString("bannerName"));
        return account;
    }


    @Override
    public void insertProfile(Account account) {

    }

    @Override
    public void insertSignon(Account account) {

    }

    @Override
    public void updateAccount(Account account) {

    }

    @Override
    public void updateProfile(Account account) {

    }

    @Override
    public void updateSignon(Account account) {

    }

    public static void main(String[] args) {
        AccountDao accountDao = new AccountDaoImpl();
        Account account = new Account();
        account.setUsername("j2ee");
        account.setPassword("j2ee");
        Account result = accountDao.getAccountByUsernameAndPassword(account);
        System.out.println("success");
    }
}